package com.scalar.chiptrack.operations.process.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.operations.process.forms.ProductSubstitutablesForm;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import org.apache.struts.util.LabelValueBean;

public class ProductSubstitutablesDAO
{
    public static ArrayList<LabelValueBean> getProducts(String productType) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList<LabelValueBean> productList = new ArrayList<LabelValueBean> ();
        String product;
        long processStepID;

        if ( productType != null && productType.trim().length() > 0 && ( ( productType.trim().equalsIgnoreCase("CHIP") ||
             productType.trim().equalsIgnoreCase("BOARD") || productType.trim().equalsIgnoreCase("KIT") || productType.trim().equalsIgnoreCase("Die Bank") ) ))
        {
            try
            {
                conn = DataSource.getConnection ();

                if ( productType.trim().equalsIgnoreCase("CHIP") )
                {
                    pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.productSubstitutes.products_Chips" ) );
                }
                else if (( productType.trim().equalsIgnoreCase("BOARD") || productType.trim().equalsIgnoreCase("KIT") ) )
                {
                    pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.productSubstitutes.products_KitsOrBoards" ) );
                    pstmt.setString(1, productType );
                }
                else if( productType.trim().equalsIgnoreCase("Die Bank") )
                {
                	pstmt = conn.prepareStatement( "SELECT P.Part_Number, P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_Id = PS.ProcessStep_ID AND PS.PS_Type = 'Die Bank'" );
                }

                rs = pstmt.executeQuery ();

                while ( rs.next () )
                {
                    product = rs.getString("Part_Number");
                    processStepID = rs.getLong("ProcessStep_ID");
                    productList.add( new LabelValueBean( product.trim () , String.valueOf(processStepID)));

                }
            }
            catch ( Exception sqle )
            {
                sqle.printStackTrace ();
                throw new ScalarException ( "Could not load Products from Data Base" , sqle );
            }
            finally
            {
                if ( rs != null )
                {
                    try {
                        rs.close ();
                    }
                    catch ( SQLException ignore )
                    {
                    }
                }
                if ( pstmt != null )
                {
                    try
                    {
                        pstmt.close ();
                    }
                    catch ( SQLException ignore )
                    {
                    }
                }
                if ( conn != null )
                {
                    try
                    {
                        conn.close ();
                    }
                    catch ( SQLException ignore )
                    {
                    }
                }
            }
        }
        return productList;
    }

    /*
    * This method takes productType and selectedProductProcessStepID as parameters.
    * retuns the Substitutable Products ArrayList.
    * This will filter some Products from ArrayList, if they already selected as Substitutable Products.
    */
    public static ArrayList<LabelValueBean> getSubustitutableProducts(String productType, int selectedProductProcessStepID) throws ScalarException
    {
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rs 			= null;
        String product;
        int processStepID 		= 0;
        int sProcessStepID 		= 0;

        ArrayList<LabelValueBean> substitutableProductList = new ArrayList<LabelValueBean>();
        Map<String, String> substitutedProductsMap = new HashMap<String, String>();

        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.productSubstitutes.getSubstitutedProcessStepId"));
            if ( selectedProductProcessStepID > 0 )
            {
                pstmt.setInt ( 1 , selectedProductProcessStepID );
                rs = pstmt.executeQuery();
                while ( rs.next() )
                {
                    sProcessStepID = rs.getInt ("Substitute_Product_ProcessStep_Id");
                    substitutedProductsMap.put( sProcessStepID + "", sProcessStepID + "");
                }
            }
        }
        catch( Exception e )
        {
            e.printStackTrace ();
            throw new ScalarException ( "Could not load Selected Substitutable Products from database" , e );
        }

        if ( productType != null && productType.trim().length() > 0 && ( ( productType.trim().equalsIgnoreCase("CHIP") ||
             productType.trim().equalsIgnoreCase("BOARD") || productType.trim().equalsIgnoreCase("KIT") || productType.trim().equalsIgnoreCase("Die Bank") ) ))
        {
            try
            {
                //If productType is "CHIP", first finding the ProcessStepID of the Assembly Process from which this
                //selectedProductProcessStepID ("Finished Good Process") came and then collecting all the Finished Goods
                //coming from that Assembly. That list nothing but the required Substitutable Products.

                if( productType.trim().equalsIgnoreCase("CHIP") )
                {
                    //Getting the assembly ProcessStepID for the ProcessStepID.
                    /* pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.productSubstitutes.assembly_processstep_id_for_fg_processstep_id" ) );
                    pstmt.setInt(1, selectedProductProcessStepID);
                    rs = pstmt.executeQuery();
                    if (rs != null && rs.next())
                    {
                        assemblyProcessStepID = rs.getInt(1);
                    }

                    if (rs != null)
                    {
                        rs.close();
                    }

                    if (pstmt != null)
                    {
                        pstmt.close();
                    }                 */

                    //Getting the Substitutable Products.
                    pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.productSubstitutes.product_substitutes_for_Chips" ) );
                    //pstmt.setInt(1, assemblyProcessStepID);
                    rs = pstmt.executeQuery();

                    while(rs.next())
                    {
                        product = rs.getString("Part_Number");
                        processStepID = rs.getInt("ProcessStep_ID");

                        if ( (processStepID != selectedProductProcessStepID) && (substitutedProductsMap.get(processStepID + "") == null ))
                        {
                            substitutableProductList.add(new LabelValueBean( product.trim(), String.valueOf(processStepID)));
                        }
                    }
                }
                else if (( productType.trim().equalsIgnoreCase("BOARD") || productType.trim().equalsIgnoreCase("KIT") ) )
                {
                    pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.productSubstitutes.products_KitsOrBoards" ) );
                    pstmt.setString(1, productType );

                    rs = pstmt.executeQuery ();

                    while ( rs.next () )
                    {
                        product = rs.getString("Part_Number");
                        processStepID = rs.getInt("ProcessStep_ID");
                        if ( selectedProductProcessStepID != processStepID && (substitutedProductsMap.get(String.valueOf(processStepID)) == null ))
                        {
                            substitutableProductList.add( new LabelValueBean( product.trim () , String.valueOf (processStepID) ) );
                        }
                    }
                }
                else if( productType.trim().equalsIgnoreCase("Die Bank") )
                {
                	pstmt = conn.prepareStatement( "SELECT P.Part_Number, P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_Id = PS.ProcessStep_ID AND PS.PS_Type = 'Die Bank'" );
                    rs = pstmt.executeQuery();

                    while(rs.next())
                    {
                        product = rs.getString("Part_Number");
                        processStepID = rs.getInt("ProcessStep_ID");

                        if ( (processStepID != selectedProductProcessStepID) && (substitutedProductsMap.get(processStepID + "") == null ))
                        {
                            substitutableProductList.add(new LabelValueBean( product.trim(), String.valueOf(processStepID)));
                        }
                    }
                }

            }
            catch ( Exception sqle )
            {
                sqle.printStackTrace ();
                throw new ScalarException ( "Could not load Substitutable Products from database" , sqle );
            }
            finally
            {
                if ( rs != null )
                {
                    try {
                        rs.close ();
                    }
                    catch ( SQLException ignore )
                    {
                    }
                }
                if ( pstmt != null )
                {
                    try
                    {
                        pstmt.close ();
                    }
                    catch ( SQLException ignore )
                    {
                    }
                }
                if ( conn != null )
                {
                    try
                    {
                        conn.close ();
                    }
                    catch ( SQLException ignore )
                    {
                    }
                }
            }
        }
        return substitutableProductList;
    }

    /*
    * This method takes productType and selectedProductProcessStepID as parameters.
    * retuns the Substitutable Products ArrayList.
    * This will filter some Products from ArrayList, if they already selected as Substitutable Products.
    */
    public static ArrayList<LabelValueBean> getSubustitutableProducts( int selectedProductProcessStepID ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList<LabelValueBean> substitutableProductList = new ArrayList<LabelValueBean>();  //Stores Substitutable Products.

        String product;
        int processStepID = 0;                                  //represents the ProcessStepID of the productName.

        //Collecting all the Selected Substitutable Products for the selectedProductProcessStepID into substitutedProductsMap
        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement("SELECT A.PROCESSSTEP_ID,PART_NUMBER FROM PRODUCT A,PROCESS_STEP B WHERE P_PARENT_PRODUCT_ID = ( SELECT P_PARENT_PRODUCT_ID FROM PRODUCT WHERE PROCESSSTEP_ID = ? ) AND A.PROCESSSTEP_ID = B.PROCESSSTEP_ID AND B.PS_TYPE LIKE 'Blank FGI' AND B.PS_TYPE_NAME LIKE 'Class Store' AND A.PROCESSSTEP_ID NOT IN ( SELECT SUBSTITUTE_PRODUCT_PROCESSSTEP_ID FROM PRODUCT_SUBSTITUTABLES_TABLE WHERE PRODUCT_PROCESSSTEP_ID = ? ) AND A.PROCESSSTEP_ID <> ? ORDER BY PART_NUMBER");
            if ( selectedProductProcessStepID > 0 )
            {
                pstmt.setInt ( 1 , selectedProductProcessStepID );
                pstmt.setInt ( 2 , selectedProductProcessStepID );
                pstmt.setInt ( 3 , selectedProductProcessStepID );

                rs = pstmt.executeQuery();
                while ( rs.next() )
                {
                    product = rs.getString("PART_NUMBER");
                    processStepID = rs.getInt("PROCESSSTEP_ID");
                    substitutableProductList.add( new LabelValueBean( product.trim () , String.valueOf (processStepID) ) );
                }
            }
        }
        catch( Exception e )
        {
            e.printStackTrace ();
            throw new ScalarException ( "Could not load Selected Substitutable Products from database" , e );
        }
        finally {
           if (rs != null) {
               try { rs.close(); } catch (SQLException ignore) {}
           }
           if (pstmt != null) {
               try { pstmt.close(); } catch (SQLException ignore){}
           }
           if (conn != null) {
               try { conn.close(); } catch (SQLException ignore){}
           }
        }
        return substitutableProductList;
    }
    public static boolean insertSubstitutableProcessStepId( DBTransaction dbTransaction, ProductSubstitutablesForm productSubstitutablesForm , boolean isInsert) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try {

            if (dbTransaction == null) {
               conn = DataSource.getConnection();
            }
            else {
               conn = dbTransaction.getConnection();
            }

            int processStepId = 0;
            int tmpCount = 0;
            processStepId = Integer.parseInt ( productSubstitutablesForm.getSelectedProduct() ) ;

            String[] substituteProcessStepId = productSubstitutablesForm.getSelectedSubstitutableProducts();
            int  sProcessStepId = 0;

            //Deleting all the Substitutable Products for this Product.
            pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.productSubstitutes.deleteProduct_Substitutes"));
            pstmt.setInt ( 1 , processStepId );
            pstmt.executeUpdate() ;

            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }

            //Saving the Substitutable Products into the DB
            if (substituteProcessStepId != null && substituteProcessStepId.length > 0 )
            {
               pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.productSubstitutes.saveProduct_Substitutes"));

                for ( int i = 0 ; i < substituteProcessStepId.length ; i++ )
                {
                    if (substituteProcessStepId [ i ] != null && substituteProcessStepId [ i ].length() > 0 )
                    {

                        try {
                            sProcessStepId =  Integer.parseInt ( substituteProcessStepId [ i ] );
                        }
                        catch( NumberFormatException nfe)
                        {
                            sProcessStepId = 0;
                        }

                        if ( sProcessStepId > 0 && processStepId > 0 )
                        {
                            pstmt.setInt ( 1 , processStepId );
                            pstmt.setInt ( 2 , sProcessStepId );
                            pstmt.addBatch();
                            tmpCount++;
                        }
                    }
                }

                if ( tmpCount > 0 )
                {
                    pstmt.executeBatch();
                }
            }
            success = true;

            } catch (Exception sqle) {
               sqle.printStackTrace();
               throw new ScalarException("Could not insert External User Info into database", sqle);
            }
            finally {
                if (dbTransaction == null) {
                   try {
                       if (conn != null) {
                           if (success == true) {
                               conn.commit();
                           }
                           else {
                               conn.rollback();
                           }
                       }
                   }
                   catch (SQLException ignore) {}
                }
                if (rs != null) {
                   try { rs.close(); } catch (SQLException ignore) {}
                }
                if (pstmt != null) {
                   try { pstmt.close(); } catch (SQLException ignore){}
                }
                if (conn != null) {
                   try { conn.close(); } catch (SQLException ignore){}
                }
           }

           return success;
    }

    /*
     *  This method returns the Selected Substitutables.
     */

    public static ArrayList<LabelValueBean> retriveSubstitutableProcessStepIds ( DBTransaction dbTransaction, String processStepId ) throws ScalarException
    {
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rs 			= null;
        int sProcessStepID 		= 0;
        int psId 				= 0;
        String partNumber 		= null ;
        ArrayList<LabelValueBean> substitutableProductList = new ArrayList<LabelValueBean>();

        try
        {
            if (dbTransaction == null) {
               conn = DataSource.getConnection();
            }
            else {
               conn = dbTransaction.getConnection();
            }

            try
            {
                psId = Integer.parseInt( processStepId );
            }
            catch(NumberFormatException nfe)
            {
                 psId = 0;
            }

           pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.productSubstitutes.getSubstitutedProcessStepId"));

            if ( psId > 0 )
            {
                pstmt.setInt ( 1 , psId );
                rs = pstmt.executeQuery();
                while ( rs.next() )
                {
                    partNumber		=  rs.getString("Part_Number");
                    sProcessStepID 	= rs.getInt("Substitute_Product_ProcessStep_Id");
                    substitutableProductList.add( new LabelValueBean( partNumber.trim () , String.valueOf ( sProcessStepID ) ) );
                }
            }

        } catch (Exception sqle) {
           sqle.printStackTrace();
           throw new ScalarException("Unable to retrieve Substitutede Products", sqle);
        }
        finally {
           if (rs != null) {
               try { rs.close(); } catch (SQLException ignore) {}
           }
           if (pstmt != null) {
               try { pstmt.close(); } catch (SQLException ignore){}
           }
           if (conn != null) {
               try { conn.close(); } catch (SQLException ignore){}
           }
        }

        return substitutableProductList;
   }

    /**
     * This method returns the Selected Substitutables, for the Program Release.
     * @param releaseId
     * @param processStepId
     * @param loadMappedParts is true -> product substitutables for blank part no(Product_Substitutables_Table table) +
     *                                   selected product substitutables for the blank part no for the current program
     *                                   release(Prog_Release_Substitued_Part_Numbers table)
     *        loadMappedParts is false -> product substitutables for blank part no(Product_Substitutables_Table table) only
     * @return
     * @throws ScalarException
     */
    public static ArrayList<PartNumber> retriveSubstitutableProcessStepIdsForProgramRelease ( int releaseId, String processStepId, boolean loadMappedParts ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;

        ArrayList<PartNumber> substitutableProductList = new ArrayList<PartNumber> ();
        int psId = 0;
        try
        {
            conn = DataSource.getConnection();
            try
            {
                psId = Integer.parseInt( processStepId );
            }
            catch(NumberFormatException nfe)
            {
                 psId = 0;
            }
            if ( psId > 0 )
            {
                cstmt 	= conn.prepareCall( SqlQueryMappings.get("operations.program.release.getSubstitutablePartNos") );
                cstmt.setInt ( 1, releaseId );
                cstmt.setInt ( 2, psId );
                cstmt.setInt ( 3, loadMappedParts?0:1);
                rs = cstmt.executeQuery();
                while ( rs.next() )
                {
                    PartNumber partNumber = new PartNumber();
                    partNumber.setProcessStepId(""+rs.getInt("Substitute_Product_ProcessStep_Id"));
                    partNumber.setPartNumber(rs.getString("Part_Number"));
                    partNumber.setSelected(rs.getInt("Is_Selected")==0);
                    substitutableProductList.add( partNumber );
                }
            }

        } catch (Exception sqle) {
           sqle.printStackTrace();
           throw new ScalarException("Unable to retrieve Substituted Products", sqle);
        }
        finally {
           if (rs != null) {
               try { rs.close(); } catch (SQLException ignore) {}
           }
           if (cstmt != null) {
               try { cstmt.close(); } catch (SQLException ignore){}
           }
           if (conn != null) {
               try { conn.close(); } catch (SQLException ignore){}
           }
        }
        if( substitutableProductList.isEmpty())
        {
            substitutableProductList = null;
        }
        return substitutableProductList;
   }
}